<?php
/**
 * Created by PhpStorm.
 * User: mac
 * Date: 2018/8/8
 * Time: 上午9:18
 */

namespace app\extra;
use PHPExcel;
use PHPExcel_IOFactory;
use PHPExcel_Cell;
use PHPExcel_CachedObjectStorageFactory;
use PHPExcel_Settings;
class ExcelToArray
{
    public function __construct() {

    }
    /**
     * csv导入
     * @param $filename
     * @return array
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     */
    public function getCsv($filename){
        $handle = fopen($filename, 'r');
        $excelData = array ();
        $head=array();
        $n = 0;

        while ($data = fgetcsv($handle, 1000)) {
            $num = count($data);
            if($n==0){
                for ($i = 0; $i < $num; $i++) {
                    if(!empty($data[$i])){
                        $head[$i] = iconv('GBK', 'utf-8',trim($data[$i]));
                    }
                }
            }else{
                for ($j = 0; $j < count($head); $j++) {
                    $excelData[$n-1][$head[$j]] = iconv('GBK', 'utf-8',trim($data[$j]));
                }
            }
            $n++;
        }
        return $excelData;
    }
    /**
     * 根据字段名称PHPEXCEL
     * @param $filename
     * @return array
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     */
    public function readCsv($filename){

        $objReader = PHPExcel_IOFactory::createReader('CSV')->setInputEncoding('GBK');
        $objReader->setReadDataOnly(true);
        $objPHPExcel = $objReader->load($filename);
        $objWorksheet = $objPHPExcel->getActiveSheet();
        $highestRow = $objWorksheet->getHighestRow();
        $highestColumn = $objWorksheet->getHighestColumn();
        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
        $excelData = array();
        $head=array();
        for ($row = 1; $row <= $highestRow; $row++) {
            for ($col = 0; $col < $highestColumnIndex; $col++) {
                $colName=(string)$objWorksheet->getCellByColumnAndRow($col, $row)->getFormattedValue();
                if($row==1){
                    $head[]=$colName;
                }else{
                    $excelData[$row-1][$head[$col]] =$colName;
                }
            }
        }
        return $excelData;
    }
    /**
     * 根据字段名称
     * @param $filename
     * @return array
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     */
    public function read($filename){

        $objReader = PHPExcel_IOFactory::createReader('Excel2007');
        $objReader->setReadDataOnly(true);
        $objPHPExcel = $objReader->load($filename);
        $objWorksheet = $objPHPExcel->getActiveSheet();
        $highestRow = $objWorksheet->getHighestRow();
        $highestColumn = $objWorksheet->getHighestColumn();
        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
        $excelData = array();
        $head=array();

        for ($row = 1; $row <= $highestRow; $row++) {
            for ($col = 0; $col < $highestColumnIndex; $col++) {
                $colName=(string)$objWorksheet->getCellByColumnAndRow($col, $row)->getFormattedValue();
                if($row==1){
                    $head[] = $colName;
                }else{
                    $excelData[$row-1][$head[$col]] = $colName;
                }
            }
        }

        return $excelData;
    }

    /**
     * 根据列的数量
     * @param $filename
     * @return array
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     */
    public function readColIndex($filename){
        $objReader = PHPExcel_IOFactory::createReader('Excel2007');
        $objReader->setReadDataOnly(true);
        $objPHPExcel = $objReader->load($filename);
        $objWorksheet = $objPHPExcel->getActiveSheet();
        $highestRow = $objWorksheet->getHighestRow();
        $highestColumn = $objWorksheet->getHighestColumn();
        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
        $excelData = array();
        for ($row = 1; $row <= $highestRow; $row++) {
            for ($col = 0; $col < $highestColumnIndex; $col++) {
                $colName=(string)$objWorksheet->getCellByColumnAndRow($col, $row)->getFormattedValue();
                $excelData[$row][] =$colName;
            }
        }
        return $excelData;
    }

    /**
     * 根据字段名称（可获取多个sheet）
     */
    public function readSheets($filename){
        $objReader = PHPExcel_IOFactory::createReader('Excel2007');
        $objReader->setReadDataOnly(true);
        $objPHPExcel = $objReader->load($filename);
        $sheetCount = $objPHPExcel->getSheetCount(); //获取sheet工作表总个数
        $name=$objPHPExcel->getSheetNames(); //所有sheet的名字

        $excelData = array();

        for ($i =0;$i < $sheetCount;$i++){
            $sheet = $objPHPExcel->getSheet($i);
            $highestRow = $sheet->getHighestRow();
            $highestColumn = $sheet->getHighestColumn();
            $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
            $head=array();
            for ($row = 1; $row <= $highestRow; $row++) {
                for ($col = 0; $col < $highestColumnIndex; $col++) {
                    $colName=(string)$sheet->getCellByColumnAndRow($col, $row)->getFormattedValue();
                    if($row==1){
                        $head[] = $colName;
                    }else{
                        $excelData[$name[$i]][$row-1][$head[$col]] = $colName;
                    }
                }
            }

        }

        return $excelData;
    }

    /**
     * 根据列的数量（可获取多个sheet）
     */
    public function readColIndexSheets($filename){
        $objReader = PHPExcel_IOFactory::createReader('Excel2007');
        $objReader->setReadDataOnly(true);
        $objPHPExcel = $objReader->load($filename);
        $sheetCount = $objPHPExcel->getSheetCount(); //获取sheet工作表总个数
        $name=$objPHPExcel->getSheetNames(); //所有sheet的名字

        $excelData = array();
        for ($i =0;$i < $sheetCount;$i++){
            $sheet = $objPHPExcel->getSheet($i);
            $highestRow = $sheet->getHighestRow();
            $highestColumn = $sheet->getHighestColumn();
            $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);

            for ($row = 1; $row <= $highestRow; $row++) {
                for ($col = 0; $col < $highestColumnIndex; $col++) {
                    $colName=(string)$sheet->getCellByColumnAndRow($col, $row)->getFormattedValue();
                    $excelData[$name[$i]][$row][] =$colName;
                }
            }

        }
        return $excelData;
    }

    public function readAllIndexSheets($filename){
        $objReader = PHPExcel_IOFactory::createReader('Excel2007');
        $objReader->setReadDataOnly(true);
        $objPHPExcel = $objReader->load($filename);
        $sheetCount = $objPHPExcel->getSheetCount(); //获取sheet工作表总个数
        $name=$objPHPExcel->getSheetNames(); //所有sheet的名字
        return $name;
    }

    /**
     * 根据指定Sheet页,同时返回文件sheet数量
     * by freethy 20190710
     * @param $filename 文件
     * @param $index sheet页索引
     */
    public function readSheet($filename, $index){
        $objReader = PHPExcel_IOFactory::createReader('Excel2007');
        $objReader->setReadDataOnly(true);
        $objPHPExcel = $objReader->load($filename);
        $sheetCount = $objPHPExcel->getSheetCount();
        $names = $objPHPExcel->getSheetNames();
        $name = $names[$index];

        $data = array();
        $sheet = $objPHPExcel->getSheet($index);
        $highestRow = $sheet->getHighestRow();
        $highestColumn = $sheet->getHighestColumn();
        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
        for ($row = 1; $row <= $highestRow; $row++) {
            for ($col = 0; $col < $highestColumnIndex; $col++) {
                $colName=(string)$sheet->getCellByColumnAndRow($col, $row)->getFormattedValue();
                $data[$row][] =$colName;
            }
        }
        return ['name'=>$name,'count'=>$sheetCount,'data'=>$data];
    }

}